{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "norwegian-saturday",
   "metadata": {},
   "source": [
    "# 通用模板自动填充库应用演示\n",
    "\n",
    "> * 准备工作\n",
    ">   - 导入包\n",
    ">   - 模板文件\n",
    ">   - 填充定位\n",
    ">   - 填充内容文件\n",
    "> * 应用操作\n",
    ">   - 填充内容分割\n",
    ">     - 提取一对一映射表\n",
    ">     - 提取一对多映射表\n",
    ">     - 打包\n",
    ">   - 创建 Exsheet 对象\n",
    ">   - 读入模板\n",
    ">   - 准备空白 Excel 工作表\n",
    ">   - 在工作表中逐一放入模板块\n",
    ">     - 拆包填充内容\n",
    ">     - 新块\n",
    ">     - 填充内容\n",
    ">     - 放入 Excel 的 Sheet 表\n",
    ">   - 对 Excel 的 Sheet 进行布局设置\n",
    ">   - 保存为 Excel 文件\n",
    "___"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "polish-exploration",
   "metadata": {},
   "source": [
    "1. 准备工作\n",
    "<br>1.1 导入包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "thorough-dinner",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from openpyxl import Workbook\n",
    "import fillpat as fp\n",
    "import fin"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "earlier-theme",
   "metadata": {},
   "source": [
    "    1.2 模板文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "faced-sugar",
   "metadata": {},
   "outputs": [],
   "source": [
    "pat = r'.\\datas\\请款单模板.xlsx'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "imperial-modeling",
   "metadata": {},
   "source": [
    "    1.3 填充定位"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "whole-turkey",
   "metadata": {},
   "outputs": [],
   "source": [
    "oto_loc = {\n",
    "    'M2' :'填单日期',\n",
    "    'E3' :'收款单位',\n",
    "    'M3' :'请款部门',\n",
    "    'E4':'银行账号',\n",
    "    'M4':'开户银行',\n",
    "    'F5':'大写金额',\n",
    "    'M5':'小写金额',\n",
    "    'B7':'用途',\n",
    "    'O9':'附件数量',\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "documented-quantity",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 不需要 多对多映射\n",
    "mtm_loc = None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "athletic-tyler",
   "metadata": {},
   "source": [
    "    1.4 填充内容文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "collect-planner",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>填单日期</th>\n",
       "      <th>收款单位</th>\n",
       "      <th>请款部门</th>\n",
       "      <th>银行账号</th>\n",
       "      <th>开户银行</th>\n",
       "      <th>小写金额</th>\n",
       "      <th>用途</th>\n",
       "      <th>附件数量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-03</td>\n",
       "      <td>张三</td>\n",
       "      <td>业务部</td>\n",
       "      <td>2222 2222</td>\n",
       "      <td>中国银行</td>\n",
       "      <td>150000</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2022-02-04</td>\n",
       "      <td>李四</td>\n",
       "      <td>业务部</td>\n",
       "      <td>3333 3333</td>\n",
       "      <td>建设银行</td>\n",
       "      <td>251400</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2022-01-03</td>\n",
       "      <td>张三</td>\n",
       "      <td>业务部</td>\n",
       "      <td>2222 2222</td>\n",
       "      <td>中国银行</td>\n",
       "      <td>300000</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2022-02-04</td>\n",
       "      <td>李四</td>\n",
       "      <td>业务部</td>\n",
       "      <td>3333 3333</td>\n",
       "      <td>建设银行</td>\n",
       "      <td>500000</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号       填单日期 收款单位 请款部门       银行账号  开户银行    小写金额   用途  附件数量\n",
       "0   1 2022-01-03   张三  业务部  2222 2222  中国银行  150000  废钢款     1\n",
       "1   2 2022-02-04   李四  业务部  3333 3333  建设银行  251400  废钢款     2\n",
       "2   3 2022-01-03   张三  业务部  2222 2222  中国银行  300000  废钢款     1\n",
       "3   4 2022-02-04   李四  业务部  3333 3333  建设银行  500000  废钢款     2"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fil = pd.read_excel(r'.\\datas\\请款单明细表.xlsx')\n",
    "fil"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "executive-gilbert",
   "metadata": {},
   "source": [
    "2. 应用操作：\n",
    "<br>2.1 填充内容分割\n",
    "   <br>2.1.1 提取一对一映射表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "smaller-upper",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 无此步骤"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "removed-million",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>填单日期</th>\n",
       "      <th>收款单位</th>\n",
       "      <th>请款部门</th>\n",
       "      <th>银行账号</th>\n",
       "      <th>开户银行</th>\n",
       "      <th>小写金额</th>\n",
       "      <th>用途</th>\n",
       "      <th>附件数量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-03</td>\n",
       "      <td>张三</td>\n",
       "      <td>业务部</td>\n",
       "      <td>2222 2222</td>\n",
       "      <td>中国银行</td>\n",
       "      <td>150000</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2022-02-04</td>\n",
       "      <td>李四</td>\n",
       "      <td>业务部</td>\n",
       "      <td>3333 3333</td>\n",
       "      <td>建设银行</td>\n",
       "      <td>251400</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2022-01-03</td>\n",
       "      <td>张三</td>\n",
       "      <td>业务部</td>\n",
       "      <td>2222 2222</td>\n",
       "      <td>中国银行</td>\n",
       "      <td>300000</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2022-02-04</td>\n",
       "      <td>李四</td>\n",
       "      <td>业务部</td>\n",
       "      <td>3333 3333</td>\n",
       "      <td>建设银行</td>\n",
       "      <td>500000</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号       填单日期 收款单位 请款部门       银行账号  开户银行    小写金额   用途  附件数量\n",
       "0   1 2022-01-03   张三  业务部  2222 2222  中国银行  150000  废钢款     1\n",
       "1   2 2022-02-04   李四  业务部  3333 3333  建设银行  251400  废钢款     2\n",
       "2   3 2022-01-03   张三  业务部  2222 2222  中国银行  300000  废钢款     1\n",
       "3   4 2022-02-04   李四  业务部  3333 3333  建设银行  500000  废钢款     2"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adatas = fil\n",
    "adatas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "other-determination",
   "metadata": {},
   "source": [
    "**完善合计数**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "crazy-hayes",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>填单日期</th>\n",
       "      <th>收款单位</th>\n",
       "      <th>请款部门</th>\n",
       "      <th>银行账号</th>\n",
       "      <th>开户银行</th>\n",
       "      <th>小写金额</th>\n",
       "      <th>用途</th>\n",
       "      <th>附件数量</th>\n",
       "      <th>大写金额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-03</td>\n",
       "      <td>张三</td>\n",
       "      <td>业务部</td>\n",
       "      <td>2222 2222</td>\n",
       "      <td>中国银行</td>\n",
       "      <td>150000</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>1</td>\n",
       "      <td>壹拾伍万元整</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2022-02-04</td>\n",
       "      <td>李四</td>\n",
       "      <td>业务部</td>\n",
       "      <td>3333 3333</td>\n",
       "      <td>建设银行</td>\n",
       "      <td>251400</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>2</td>\n",
       "      <td>贰拾伍万壹仟肆佰元整</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2022-01-03</td>\n",
       "      <td>张三</td>\n",
       "      <td>业务部</td>\n",
       "      <td>2222 2222</td>\n",
       "      <td>中国银行</td>\n",
       "      <td>300000</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>1</td>\n",
       "      <td>叁拾万元整</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2022-02-04</td>\n",
       "      <td>李四</td>\n",
       "      <td>业务部</td>\n",
       "      <td>3333 3333</td>\n",
       "      <td>建设银行</td>\n",
       "      <td>500000</td>\n",
       "      <td>废钢款</td>\n",
       "      <td>2</td>\n",
       "      <td>伍拾万元整</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号       填单日期 收款单位 请款部门       银行账号  开户银行    小写金额   用途  附件数量        大写金额\n",
       "0   1 2022-01-03   张三  业务部  2222 2222  中国银行  150000  废钢款     1      壹拾伍万元整\n",
       "1   2 2022-02-04   李四  业务部  3333 3333  建设银行  251400  废钢款     2  贰拾伍万壹仟肆佰元整\n",
       "2   3 2022-01-03   张三  业务部  2222 2222  中国银行  300000  废钢款     1       叁拾万元整\n",
       "3   4 2022-02-04   李四  业务部  3333 3333  建设银行  500000  废钢款     2       伍拾万元整"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adatas['大写金额'] = adatas.小写金额.apply(fin.amount_converter)\n",
    "adatas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "romantic-father",
   "metadata": {},
   "source": [
    "    2.1.2 提取一对多映射表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "subject-answer",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 无此步骤"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "enclosed-western",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 无此步骤"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acute-sherman",
   "metadata": {},
   "source": [
    "    2.1.3 打包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "palestinian-theater",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 无此步骤"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "joined-romance",
   "metadata": {},
   "source": [
    "    2.2 创建 Exsheet 对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "incorporated-helping",
   "metadata": {},
   "outputs": [],
   "source": [
    "exs = fp.Exsheet(\n",
    "    gap_between_blocks_in_a_page=5,\n",
    "    number_of_blocks_per_page=2\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "matched-startup",
   "metadata": {},
   "source": [
    "    2.3 读入模板"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "union-lexington",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<fillpat.Exblock at 0x1586fb746d0>"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exs.read_pattern(pat)\n",
    "exs.pattern"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "rotary-frame",
   "metadata": {},
   "source": [
    "    2.4 准备空白 Excel 工作表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "macro-prison",
   "metadata": {},
   "outputs": [],
   "source": [
    "wb = Workbook()\n",
    "ws = wb.active"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "collect-topic",
   "metadata": {},
   "source": [
    "    2.5 在工作表中逐一放入模板块"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "indirect-particle",
   "metadata": {},
   "outputs": [],
   "source": [
    "i = 0\n",
    "for idx,adata in adatas.iterrows():\n",
    "    # 2.5.1 拆包\n",
    "    ## 无此步骤\n",
    "    # 2.5.2 新块\n",
    "    i = i + 1\n",
    "    exs.Id = i\n",
    "    # 2.5.3 填充\n",
    "    exs.fill_blank(oto_loc, adata)\n",
    "    # 2.5.4  放入 Sheet\n",
    "    exs.put_in_sheet(ws)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "organizational-simpson",
   "metadata": {},
   "source": [
    "    2.6 对 Excel 的 Sheet 进行布局设置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "stainless-corruption",
   "metadata": {},
   "outputs": [],
   "source": [
    "exs.page_layout(ws, margins=exs.NARROWER,scale=70 ,verticalcentered=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "portuguese-disorder",
   "metadata": {},
   "source": [
    "    2.7 保存为 Excel 文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "breathing-giving",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done\n"
     ]
    }
   ],
   "source": [
    "wb.save('输出请款单.xlsx')\n",
    "print('done')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "experienced-renewal",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
